package gwtappcontainer.server.apps.insight;

import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Center;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class CenterRepository {
	
	public static Center get(String centerName) {
		return getByIdOrName(0, centerName);        	       
	}
	
	public static Center get(long centerId) {
		return getByIdOrName(centerId, null);        	       
	}
		
	private static Center getByIdOrName(long centerId, String centerName) {
        
	    try {
	    	String sql;
	    	if (centerId == 0)
	    		sql = "select center_id, center from centers where center = ?";
	    	else 
	    		sql = "select center_id, center from centers where center_id = ?";
           
            Center center = null;
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                    
                	if (centerId == 0)
                		ps.setString(1, centerName.toLowerCase());
                	else 
                		ps.setLong(1, centerId);
                   
                    try (ResultSet resultSet = ps.executeQuery()) {                                        
                        while (resultSet.next()) {
                            center = new Center();
                            center.id = resultSet.getInt(1);
                            center.name = resultSet.getString(2);
                        }
                    }
                }
            }
           
            return center;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }              
	}
	
	public static Center add(String centerName) {				 
					
		 if (null != get(centerName))
			 throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS, 
					 "Center [" + centerName + "] already exists");
		 
		 try {
		     String sql = "insert into centers values (0, ?)";                      
		    
		     try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
		            
		         try (PreparedStatement ps = connection.prepareStatement(sql)) {
		                
		             ps.setString(1, centerName.toLowerCase());
		            
		             ps.executeUpdate();                                    
		         }
		     }
		    
		     Center center = get(centerName);        
		     return center;
		        
		 } catch (Exception ex) {
		     throw new RuntimeException(ex);
		 }              
	 }

	public static void delete(String centerName) {
         
	     Center center = get(centerName);
	    
	     if (null == center)
	         throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
	                         "Center [" + centerName + "] does not exist");
	                                            
	     try {
	         String sql = "delete from centers where center = ?";                        
	        
	         try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
	                
	             try (PreparedStatement ps = connection.prepareStatement(sql)) {
	                    
	                 ps.setString(1, centerName.toLowerCase());
	                
	                 ps.executeUpdate();                                    
	             }
	         }                                                                      
	     } catch (Exception ex) {
	         throw new RuntimeException(ex);
	     }              
	 }

	public static ArrayList<Center> getAll() {
		try {
	        String sql = "select center_id, center from centers order by center";
	       
	        ArrayList<Center> centers = new ArrayList<>();
	       
	        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
	               	        	
	            try (PreparedStatement ps = connection.prepareStatement(sql)) {	                   	               
	               
	                try (ResultSet resultSet = ps.executeQuery()) {                                        
	                    while (resultSet.next()) {
	                        Center center = new Center();
	                        center.id = resultSet.getInt(1);
	                        center.name = resultSet.getString(2);
	                        
	                        centers.add(center);
	                    }
	                }
	            }
	        }
	       
	        return centers;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    } 
	}
}

